package cz.data.common.database.dialect;

import com.alibaba.fastjson.JSONObject;
import cz.data.common.support.db.core.DbColumn;
import cz.data.common.support.db.core.DbTable;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * Oracle Oracle11g及以下数据库方言
 *
 */
public class OracleDialect extends AbstractDbDialect {

    @Override
    public String tableNames(String dbName, String schema) {
        schema = StringUtils.upperCase(schema);
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT tables.table_name AS TABLENAME FROM sys.all_tables tables ");
        sb.append(" WHERE tables.owner = '").append(schema).append("'");
        sb.append(" union all ");
        sb.append("SELECT views.view_name AS TABLENAME FROM sys.all_views views");
        sb.append(" WHERE views.owner = '").append(schema).append("'");
        return sb.toString();
    }

    @Override
    public String columnNames(String dbName, String schema, String tableName) {
        if (StringUtils.isNotBlank(schema)) {
            return String.format("SELECT COLUMN_NAME FROM sys.all_tab_columns WHERE OWNER = '%s' AND TABLE_NAME = '%s'", schema, tableName);
        } else {
            return String.format("SELECT COLUMN_NAME FROM sys.user_tab_columns WHERE TABLE_NAME = '%s'", tableName);
        }
    }

    @Override
    public String columns(String dbname, String schema, String tableName) {
        schema = StringUtils.upperCase(schema);
        StringBuilder sb = new StringBuilder();
        sb.append("select columns.column_name AS colName, columns.data_type AS DATATYPE, columns.data_length AS DATALENGTH, columns.data_precision AS DATAPRECISION, ");
        sb.append(" columns.data_scale AS DATASCALE, columns.nullable AS NULLABLE, columns.column_id AS COLPOSITION, columns.data_default AS DATADEFAULT, comments.comments AS COLCOMMENT,");
        sb.append(" case when t.column_name is null then 0 else 1 end as COLKEY ");
        sb.append(" from sys.all_tab_columns columns LEFT JOIN sys.all_col_comments comments ON columns.table_name = comments.table_name AND columns.column_name = comments.column_name ");
        sb.append(" left join ( select col.column_name as column_name, con.table_name as table_name from user_constraints con, user_cons_columns col ");
        sb.append(" where con.constraint_name = col.constraint_name and con.constraint_type = 'P' ) t ");
        sb.append(" on t.table_name = columns.table_name and columns.column_name = t.column_name ");
        sb.append(" where columns.OWNER = '").append(schema).append("' ");
        sb.append(" and columns.table_name = '").append(tableName).append("' order by columns.column_id ");
        return sb.toString();
    }

    @Override
    public String tables(String dbname, String schemaName) {
        StringBuilder sb = new StringBuilder();
        sb.append("select tables.table_name AS TABLENAME, comments.comments AS TABLECOMMENT, '1' AS TABLETYPE ");
        sb.append(" from sys.all_tables tables ");
        sb.append(" left join sys.all_tab_comments comments on tables.table_name = comments.table_name ");
        sb.append(" where tables.owner = '").append(schemaName).append("'");
        sb.append(" union all ");
        sb.append("select views.view_name AS TABLENAME, comments.comments AS TABLECOMMENT, '2' AS TABLETYPE ");
        sb.append(" from sys.all_views views ");
        sb.append(" left join sys.all_tab_comments comments on views.view_name = comments.table_name ");
        sb.append(" where views.owner = '").append(schemaName).append("'");
        return sb.toString();
    }

    @Override
    public String buildPaginationSql(String originalSql, long offset, long count) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( ");
        sqlBuilder.append(originalSql).append(" ) TMP WHERE ROWNUM <=").append((offset >= 1) ? (offset + count) : count);
        sqlBuilder.append(") WHERE ROW_ID > ").append(offset);
        return sqlBuilder.toString();
    }


    @Override
    public RowMapper<DbColumn> columnMapper() {
        return (ResultSet rs, int rowNum) -> {
            DbColumn entity = new DbColumn();
            entity.setDataDefault(rs.getString("DATADEFAULT"));
            entity.setColPosition(rs.getInt("COLPOSITION"));
            entity.setDataLength(rs.getString("DATALENGTH"));
            entity.setDataPrecision(rs.getString("DATAPRECISION"));
            entity.setDataScale(rs.getString("DATASCALE"));
            entity.setColName(rs.getString("COLNAME"));
            entity.setDataType(rs.getString("DATATYPE"));
            entity.setColKey("1".equals(rs.getString("COLKEY")));
            entity.setNullable("Y".equals(rs.getString("NULLABLE")));
            entity.setColComment(rs.getString("COLCOMMENT"));
            return entity;
        };
    }

    @Override
    public RowMapper<DbTable> tableMapper() {
        return (ResultSet rs, int rowNum) -> {
            DbTable entity = new DbTable();
            entity.setTableName(rs.getString("TABLENAME"));
            entity.setTableComment(rs.getString("TABLECOMMENT"));
            String tabletype = rs.getString("TABLETYPE");
            if (StringUtils.isNotBlank(tabletype)) {
                entity.setTableType(tabletype);
            }
            return entity;
        };
    }

    @Override
    public PreparedStatementCreator upsertSQL(List<JSONObject> jsonList, String table, List<String> primaryKeys, String[] insertFields, String[] updateFields) {
        String rowData = Stream.of(insertFields)
                .map(f -> {
                    if (primaryKeys.contains(f)) {
                        return "? AS " + this.escape(f);
                    } else {
                        return "?";
                    }
                })
                .collect(Collectors.joining(","));
        String joinSQL = primaryKeys.stream()
                .map(f -> "t.".concat(this.escape(f)).concat(" = s.").concat(this.escape(f)))
                .collect(Collectors.joining(","));

        String updateSQL = Stream.of(updateFields)
                .map(this::escape)
                .map("t."::concat)
                .map(f -> f + " = ?")
                .collect(Collectors.joining(","));

        String insertFieldSQL = Stream.of(insertFields)
                .map(this::escape)
                .collect(Collectors.joining(","));

        String insertValues = Stream.of(insertFields)
                .map(f -> "?")
                .collect(Collectors.joining(","));

        StringBuilder sb = new StringBuilder();
        sb.append("MERGE INTO ").append(this.escape(table)).append(" t ");
        sb.append("USING (SELECT ").append(rowData).append(" FROM DUAL) s ");
        sb.append("ON (").append(joinSQL).append(")");
        sb.append("WHEN MATCHED THEN UPDATE SET ").append(updateSQL);
        sb.append(" WHEN NOT MATCHED THEN INSERT (").append(insertFieldSQL).append(")")
                .append(" VALUES(").append(insertValues).append(")");
        String sql = sb.toString();

        return con -> {
            PreparedStatement ps = con.prepareStatement(sql);
            for (JSONObject json : jsonList) {
                int index = 1;
                for (String insertField : insertFields) {
                    setObjectOrNULL(ps, index++, json.get(insertField));
                }
                for (String updateField : updateFields) {
                    setObjectOrNULL(ps, index++, json.get(updateField));
                }
                for (String insertField : insertFields) {
                    setObjectOrNULL(ps, index++, json.get(insertField));
                }
                ps.addBatch();
            }
            return ps;
        };
    }

    private String escape(String field) {
        return "\"" + field + "\"";
    }
}
